using System;
using NPOI.SS.UserModel;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;

namespace ExcelExtension.Util
{
    /// <summary>
    /// Excel function basis class
    /// </summary>
    internal abstract class ExcelBase : IExcel, IDisposable
    {
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="fileName">fileName</param>
        protected ExcelBase(string fileName)
        {
            FileName = fileName;
        }

        /// <summary>
        /// work book
        /// </summary>
        protected IWorkbook workbook;

        /// <summary>
        /// template File flow
        /// </summary>
        private FileStream fileStream;

        /// <summary>
        /// 
        /// </summary>
        private bool disposed;

        /// <summary>
        /// 
        /// </summary>
        public string FileName { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fs"></param>
        /// <returns></returns>
        protected virtual void InitWorkbook(FileStream fs)
        {
        }
        public void InitWorkbook()
        {
            if (File.Exists(FileName))
            {
                fileStream = new FileStream(FileName, FileMode.Open, FileAccess.Read);
            }

            InitWorkbook(fileStream);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private ISheet GetSheet(string sheetName = null)
        {
            ISheet sheet;
            if (sheetName != null)
                sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
            else
                sheet = workbook.GetSheetAt(0);
            return sheet;
        }

        public List<string> GetSheets()
        {
            var sheets = new List<string>();
            fileStream = new FileStream(FileName, FileMode.Open, FileAccess.Read);
            InitWorkbook(fileStream);
            for (var i = 0; i < workbook.NumberOfSheets; ++i)
            {
                sheets.Add(workbook.GetSheetName(i));
            }
            return sheets;
        }
        
        public int WriteDataTableToExcel(DataTable dataTable, string sheetName, bool isContainCaption, int firstRowNum = 0, int firstColNum = 0)
        {
            InitWorkbook();
            int totalCount = WriteDataTable(dataTable, sheetName, isContainCaption, firstRowNum, firstColNum);
            SaveToExcel();
            return totalCount;
        }
        public int WriteDataTable(DataTable dataTable, string sheetName, bool isContainCaption, int firstRowNum = 0, int firstColNum = 0)
        {
            var sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);
            return WriteDataTable(dataTable, sheet, isContainCaption, firstRowNum, firstColNum);
        }

        public int WriteDataTable(DataTable dataTable, ISheet sheet, bool isContainCaption, int firstRowNum = 0, int firstColNum = 0)
        {
            int totalCount = firstRowNum;
            if (isContainCaption)
            {
                var row = sheet.CreateRow(firstRowNum);
                for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; ++columnIndex)
                {
                    var caption = dataTable.Columns[columnIndex].Caption;
                    caption = string.IsNullOrWhiteSpace(caption)
                        ? dataTable.Columns[columnIndex].ColumnName
                        : caption;
                    row.CreateCell(columnIndex).SetCellValue(caption);
                }
                ++totalCount;
            }

            int rowIndex = 0;
            for (; rowIndex < dataTable.Rows.Count; ++rowIndex)
            {
                IRow row = sheet.CreateRow(totalCount);
                int sheetcolindex = firstColNum;
                for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; ++columnIndex)
                {
                    var cellValue = dataTable.Rows[rowIndex][columnIndex];
                    var value = cellValue == null || cellValue == DBNull.Value ? "" : cellValue.ToString();
                    row.CreateCell(sheetcolindex).SetCellValue(value);
                    ++sheetcolindex;
                }
                ++totalCount;
            }

            return totalCount;
        }

        public void WriteDataTable(DataTable dt, string workSheetName, bool isContainCaption, string[] fieldsToCompare, string statusColumn, string keyColumn, string summaryPrefix = "", string reviewField = null, int firstRowNum = 0, int firstColNum = 0)
        {
            var sheet = workbook.GetSheet(workSheetName) ?? workbook.CreateSheet(workSheetName);

            WriteDataTable(dt, sheet, isContainCaption, firstRowNum, firstColNum);

        }
        
        public void SaveToExcel()
        {
            // output file
            FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
            workbook.Write(fs);

            if (fs != null)
                fs.Close();
            if (workbook != null)
                workbook.Close();
        }

        public DataTable ReadDataTable(string sheetName, string[] headers, int firstRowNum = 0, int firstColNum = 0)
        {
            var data = new DataTable();

            fileStream = new FileStream(FileName, FileMode.Open, FileAccess.Read);
            InitWorkbook(fileStream);

            var sheet = GetSheet(sheetName);
            if (sheet != null)
            {
                Dictionary<DataColumn, int> columnMap = new Dictionary<DataColumn, int>();
                IRow firstRow = sheet.GetRow(firstRowNum);
                int cellCount = firstRow.LastCellNum;
                int startRow = firstRowNum;

                for (int i = 0; i < headers.Length; ++i)
                {
                    var column = new DataColumn(headers[i]);
                    data.Columns.Add(column);
                    columnMap.Add(column, i);
                }

                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    if (row == null)
                        continue;

                    DataRow dataRow = data.NewRow();
                    foreach (var map in columnMap)
                    {
                        var cell = row.GetCell(map.Value);
                        {
                            dataRow[map.Key] = cell != null ? cell.ToString().Trim() : "";
                        }
                    }
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        public DataTable ReadDataTable(string sheetName, bool isFirstRowCaption, int firstRowNum = 0, int firstColNum = 0)
        {
            var data = new DataTable();

            fileStream = new FileStream(FileName, FileMode.Open, FileAccess.Read);
            InitWorkbook(fileStream);

            var sheet = GetSheet(sheetName);
            if (sheet != null)
            {
                Dictionary<DataColumn, int> columnMap = new Dictionary<DataColumn, int>();
                IRow firstRow = sheet.GetRow(firstRowNum);
                int cellCount = firstRow.LastCellNum;

                int startRow = firstRowNum;
                if (isFirstRowCaption)
                {
                    for (int rowIndex = firstRow.FirstCellNum; rowIndex < cellCount; ++rowIndex)
                    {
                        var cell = firstRow.GetCell(rowIndex);
                        if (cell != null)
                        {
                            var cellValue = cell.ToString().Trim();
                            //if (!string.IsNullOrWhiteSpace(cellValue))
                            {
                                var column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                                columnMap.Add(column, rowIndex);
                            }
                        }
                    }
                    startRow = firstRowNum + 1;
                }

                int rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    if (row == null)
                        continue;

                    DataRow dataRow = data.NewRow();
                    foreach (var map in columnMap)
                    {
                        var cell = row.GetCell(map.Value);
                        {
                            dataRow[map.Key] = cell != null ? cell.ToString().Trim() : "";
                        }
                    }
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }
        public DataTable ReadDataTable(string sheetName, bool isFirstRowCaption)
        {
            return ReadDataTable(sheetName, isFirstRowCaption, 0, 0);
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    if (fileStream != null)
                        fileStream.Close();
                }

                fileStream = null;
                disposed = true;
            }
        }
    }
}